package com.example.myapp61.MySqlDB;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.example.myapp61.Mfragment.Home.XList;
import com.google.gson.Gson;

import org.json.JSONArray;
import org.json.JSONException;

import java.util.ArrayList;

import static android.content.ContentValues.TAG;

/**
 * 数据库配置
 */
public class DBMainHepler extends SQLiteOpenHelper {
    private SQLiteDatabase db,dbw;

    public DBMainHepler(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context,name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //sqLiteDatabase.execSQL("create table test4(id integer PRIMARY KEY AUTOINCREMENT,name varchar(10))");
        sqLiteDatabase.execSQL("create table test2(id integer PRIMARY KEY AUTOINCREMENT,_id varchar(100),messages varchar(300),name varchar(4),title varchar(20),time varchar(20),user varchar(11),status integer(1),time_add varchar(50),avatar varchar(100),gender varchar(2),label varchar(20))");
        sqLiteDatabase.execSQL("create table class(id integer(2),week integer(2),name varchar(40),site varchar(10))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    /**
     * 数据库操作
     */
    public ArrayList<XList> select(String table,int limit){
        db = getReadableDatabase();
        ArrayList<XList> arrayList = new ArrayList<XList>();
         Cursor c =  db.rawQuery("select *from "+table+ " ORDER BY id desc limit "+limit,null);
         if (c.moveToNext()){
             do {
                 String msg = c.getString(c.getColumnIndex("messages"));
                 int status =c.getInt(c.getColumnIndex("status"));
                 String _id = c.getString(c.getColumnIndex("_id"));
                 String time = c.getString(c.getColumnIndex("time"));
                 int label = c.getInt(c.getColumnIndex("label"));
//                 arrayList.add(c.getString(c.getColumnIndex("messages")));
                 arrayList.add(new XList(status,_id,msg,time,label));
             }
             while (c.moveToNext());
         }
        return arrayList;
    }

    public void save(String table,String var) throws JSONException {
        Gson gson = new Gson();
        ContentValues values = new ContentValues();
        JSONArray arr = new JSONArray(var);
        dbw = getWritableDatabase();
        db = getReadableDatabase();
        for (int i=0;i<arr.length();i++){
            XList l = gson.fromJson(arr.get(i).toString(),XList.class);
            //读取数据库,根据_id判断数据是否存在,为0 = 不存在，执行增加行为
            Cursor c =  db.rawQuery("select _id from "+table+" where _id =?",new String[]{l.get_id()});
            if (c.getCount() ==0){
                values.put("gender",l.getGender());
                values.put("time_add",l.getTime_add());
                values.put("status",l.getStatus());
                values.put("name",l.getName());
                values.put("_id",l.get_id());
                values.put("messages",l.getMessages());
                values.put("avatar",l.getAvatar());
                values.put("title",l.getTitle());
                values.put("time",l.getTime());
                values.put("user",l.getUser());
                values.put("label",l.getLabel());
                dbw.insert(table,null,values);
            }
    }
    }
}
